/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.fpt.xml.hrm.pojo;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Serializable;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import javax.xml.stream.XMLEventFactory;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLEventWriter;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.events.Attribute;
import javax.xml.stream.events.Characters;
import javax.xml.stream.events.StartElement;
import javax.xml.stream.events.XMLEvent;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.common.XMLUlti;

/**
 *
 * @author Cimela
 */
public class Timesheet implements Serializable {

    private String id;
    private String empid;
    private String datetime;
    private String description;
    private String project;
    private Double duration;
    private String status;

    public String getDatetime() {
        return datetime;
    }

    public void setDatetime(String datetime) {
        this.datetime = datetime;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Double getDuration() {
        return duration;
    }

    public void setDuration(Double duration) {
        this.duration = duration;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getProject() {
        return project;
    }

    public void setProject(String project) {
        this.project = project;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getEmpid() {
        return empid;
    }

    public void setEmpid(String empid) {
        this.empid = empid;
    }

    public Timesheet() {
    }

    public Timesheet(String id, String datetime, String description, String project, Double duration, String status) {
        this.id = id;
        this.datetime = datetime;
        this.description = description;
        this.project = project;
        this.duration = duration;
        this.status = status;
    }

    public Timesheet(String id, String empid, String datetime, String description, String project, Double duration, String status) {
        this.id = id;
        this.empid = empid;
        this.datetime = datetime;
        this.description = description;
        this.project = project;
        this.duration = duration;
        this.status = status;
    }

    public static void loadProject(HttpServletRequest request, String webPath) throws SAXException, IOException, XPathExpressionException {

        String filePath = webPath + "WEB-INF/project.xml";

        Document doc = XMLUlti.createDOMTree(filePath);

        XPathFactory xpf = XPathFactory.newInstance();
        XPath xpath = xpf.newXPath();

        String exp = "//project";

        NodeList list = (NodeList) xpath.evaluate(exp, doc, XPathConstants.NODESET);
        List lproject = new ArrayList();

        for (int i = 0; i < list.getLength(); i++) {

            int id = Integer.parseInt(list.item(i).getAttributes().getNamedItem("id").getNodeValue());

            Node children = list.item(i).getFirstChild();

//            while (!children.getNodeName().equals("id")) {
//                children = children.getNextSibling();
//            }

//            int id = new Integer(children.getTextContent());

            while (!children.getNodeName().equals("name")) {
                children = children.getNextSibling();
            }

            String pjname = children.getTextContent();
            Project pj = new Project();
            pj.setName(pjname);
            pj.setId(BigInteger.valueOf(id));

            lproject.add(pj);

        }

        HttpSession session = request.getSession();
        session.setAttribute("listPj", lproject);

    }

    public boolean deleteDB() throws NamingException, SQLException{

        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();

        String sql = "DELETE FROM DailyReport "
                + "WHERE DailyReportID = ? ";

        PreparedStatement ps = con.prepareCall(sql);
        ps.setInt(1, Integer.parseInt(this.id));

        int result =  ps.executeUpdate();

        if (result > -1){
            return true;
        }
        return false;
    }

    public boolean updateDB(HttpServletRequest request) throws NamingException, SQLException, ParseException {

        HttpSession session = request.getSession();

        List projectList = (List) session.getAttribute("listPj");

        Project temp;
        int projectid = -1;
        for (int i = 0; i < projectList.size(); i++) {
            temp = (Project) projectList.get(i);
            if (project.equals(temp.getName())) {
                projectid = temp.getId().intValue();
            }
        }

        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();

        String sql = "UPDATE DailyReport "
                + "SET Date = ?, Description = ?, ProjectID = ?, Duration = ?, Status = ? "
                + "WHERE DailyReportID = ?";

        PreparedStatement ps = con.prepareStatement(sql);
        
        ps.setDate(1, FormatUtil.stringToSqlDate(datetime, "dd/MM/yyyy"));
        ps.setString(2, description);
        ps.setInt(3, projectid);
        ps.setDouble(4, duration);
        ps.setString(5, status);
        ps.setInt(6, Integer.parseInt(id));

        int result = ps.executeUpdate();

        if (result > 0) {
            return true;
        }

        return false;
    }

    public static void createXMLTimeSheet(String filepath) throws IOException, XMLStreamException {

        Connection con = null;
        PreparedStatement ps = null;

        try {

            Context ctx = new InitialContext();
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("DBCon");
            con = ds.getConnection();

            String sql = "SELECT dr.DailyReportID, dr.EmployeeID, dr.Date, dr.Description, p.ProjectName, dr.Duration, dr.Status " 
                    + "FROM DailyReport dr INNER JOIN Project p ON dr.ProjectID = p.ProjectID "
                    + "ORDER BY dr.EmployeeID, dr.Date";
            
            ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            XMLOutputFactory xof = XMLOutputFactory.newInstance();
            XMLEventFactory xef = XMLEventFactory.newInstance();
            FileWriter file = new FileWriter(filepath);
            XMLEventWriter writer = xof.createXMLEventWriter(file);
            XMLEvent event = xef.createStartDocument();
            writer.add(event);
            //start root
            event = xef.createStartElement("", null, "dailyreport");
            writer.add(event);
            while (rs.next()) {
                String id = rs.getString("DailyReportID");
                String empid = rs.getString("EmployeeID");
                String date = FormatUtil.formatDate(FormatUtil.dateToDate(rs.getDate("Date")), "yyyy-MM-dd", Locale.ENGLISH);
                String des = rs.getString("Description");
                String project = rs.getString("ProjectName");
                Double duration = rs.getDouble("Duration");
                String status = rs.getString("Status");
                DecimalFormat twoDForm = new DecimalFormat("#.##");
                duration = Double.valueOf(twoDForm.format(duration));
                //timesheet
                event = xef.createStartElement("", null, "timesheet");
                writer.add(event);
                event = xef.createAttribute("id", id);
                writer.add(event);
                //employeeid
                event = xef.createStartElement("", null, "employeeid");
                writer.add(event);
                event = xef.createCharacters(empid);
                writer.add(event);
                event = xef.createEndElement("", null, "employeeid");
                writer.add(event);
                //date
                event = xef.createStartElement("", null, "date");
                writer.add(event);
                event = xef.createCharacters(date);
                writer.add(event);
                event = xef.createEndElement("", null, "date");
                writer.add(event);
                //description
                event = xef.createStartElement("", null, "description");
                writer.add(event);
                event = xef.createCharacters(des);
                writer.add(event);
                event = xef.createEndElement("", null, "description");
                writer.add(event);
                //project
                event = xef.createStartElement("", null, "project");
                writer.add(event);
                event = xef.createCharacters(project);
                writer.add(event);
                event = xef.createEndElement("", null, "project");
                writer.add(event);
                //duration
                event = xef.createStartElement("", null, "duration");
                writer.add(event);
                event = xef.createCharacters(String.valueOf(duration));
                writer.add(event);
                event = xef.createEndElement("", null, "duration");
                writer.add(event);
                //status
                event = xef.createStartElement("", null, "status");
                writer.add(event);
                event = xef.createCharacters(status);
                writer.add(event);
                event = xef.createEndElement("", null, "status");
                writer.add(event);
                event = xef.createEndElement("", null, "timesheet");
                writer.add(event);
            }
            //end root
            event = xef.createEndElement("", null, "dailyreport");
            writer.add(event);
            writer.flush();
            writer.close();
        } catch (NamingException ex) {
            Logger.getLogger(Timesheet.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Timesheet.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if (ps != null){
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Timesheet.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (con != null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Timesheet.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public static List loadXML(String filepath) throws FileNotFoundException, XMLStreamException {

        List temp = new ArrayList();
        String id = "";
        String empid = "";
        String datetime = "";
        String description = "";
        String project = "";
        Double duration = -1.0;
        String status = "";
        int flag;

        XMLInputFactory xif = XMLInputFactory.newInstance();
        Reader fr = new FileReader(new File(filepath));
        XMLEventReader reader = xif.createXMLEventReader(fr);
        XMLEvent event;
        flag = 0;
        while (reader.hasNext()) {

            event = reader.nextEvent();

            if (event.isStartElement()) {
                StartElement element = (StartElement) event;
                
                if (element.getName().getLocalPart().equals("timesheet")) {

                    Iterator iter = element.getAttributes();
                    while (iter.hasNext()) {
                        Attribute att = (Attribute) iter.next();
                        if (att.getName().getLocalPart().equals("id")) {
                            id = att.getValue();
                        }
                    }
                    
                } else if (element.getName().getLocalPart().equals("employeeid")) {
                    flag = 1;
                } else if (element.getName().getLocalPart().equals("date")) {
                    flag = 2;
                } else if (element.getName().getLocalPart().equals("description")) {
                    flag = 3;
                } else if (element.getName().getLocalPart().equals("project")) {
                    flag = 4;
                } else if (element.getName().getLocalPart().equals("duration")) {
                    flag = 5;
                } else if (element.getName().getLocalPart().equals("status")) {
                    flag = 6;
                }
            }

            if (event.isEndElement()) {
                flag = 0;
            }

            if (event.isCharacters()) {
                Characters charac = (Characters) event;
                switch (flag) {
                    case 1: //employeeid
                        empid = charac.getData();
                        break;
                    case 2: //date
                        datetime = charac.getData();
                        break;
                    case 3: //description
                        description = charac.getData();
                        break;
                    case 4: //project
                        project = charac.getData();
                        break;
                    case 5: //duration
                        duration = Double.parseDouble(charac.getData());
                        break;
                    case 6: //status
                        status = charac.getData();
                        temp.add(new Timesheet(id, empid, datetime, description, project, duration, status));
                        break;
                }
            }
        }

        return temp;

    }

}
